package com.px.idal;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.px.model.ProjectInfo;

/**
 *项目信息表接口
 */
public interface IProjectInfoMapper {
	/**
	 * 查询方法
	 * 查询所有项目
	 * 使用@Select 注解指明getAllProjects方法要执行的SQL
	 * */
	@Select("select * from project_info_tb")
	public List<ProjectInfo> getAllProjects();

	/**
	 * 查询方法
	 * 查询所有项目
	 * 使用@Select 注解指明getProjectByProjectInfoId方法要执行的SQL
	 * */
	@Select("select * from project_info_tb where project_info_id=#{project_info_id}")
	public List<ProjectInfo> getProjectByProjectInfoId(ProjectInfo project);

	/**
	 * 查询方法
	 * 根据公司员工id查找项目信息
	 * 使用@Select 注解指明getProjectByProjectInfoId方法要执行的SQL
	 * */
	@Select("select * from project_info_tb where company_user_id=#{company_user_id}")
	public List<ProjectInfo> getProjectByCompanyUserId(ProjectInfo project);

	/**
	 * Insert方法
	 * 增加用户
	 * 传参为对象方法
	 * */
	@Insert("insert into project_info_tb(project_class_id,project_name,project_tip,project_content,company_id,company_user_id,project_reasion,project_time,project_check,counts,average)VALUES(#{project_class_id},#{project_name},#{project_tip},#{project_content},#{company_id},#{company_user_id},#{project_reasion},#{project_time},#{project_check},#{counts},#{average})")
	public int insertProject(ProjectInfo project);

	/**
	 * 更新方法
	 * 更新指定project_info_id的项目信息
	 * */
	@Update("update project_info_tb set project_class_id=#{project_class_id},project_name=#{project_name},project_tip=#{project_tip},project_content=#{project_content},company_id=#{company_id},company_user_id=#{company_user_id},project_reasion=#{project_reasion},project_time=#{project_time},project_check=#{project_check} where project_info_id=#{project_info_id}")
	public int updateProjectByProjectInfoId(ProjectInfo project);

	/**
	 * 更新方法
	 * 更新指定project_info_id的项目分数
	 * */
	@Update("update project_info_tb set counts=#{counts},average=#{average} where project_info_id=#{project_info_id}")
	public int updateProjectScoreByProjectInfoId(ProjectInfo project);

	/**
	 * 删除方法
	 * 删除指定project_info_id的项目
	 * */
	@Delete("delete from project_info_tb where project_info_id=#{project_info_id}")
	public int deleteProjectByProjectInfoId(ProjectInfo project);

	/**
	 * 查询项目信息总条数
	 * 
	 * */
	@Select("select count(*) from project_info_tb")
	public int getAllProjectCounts();

	/**
	 * 查询指定名称项目是否存在
	 * 
	 * */
	@Select("select count(*) from project_info_tb where project_name=#{project_name}")
	public int checkProjectNameIsExist(ProjectInfo project);

	/**
	 * 查询指定id项目是否存在
	 * 
	 * */
	@Select("select count(*) from project_info_tb where project_info_id=#{project_info_id}")
	public int checkProjectInfoIdIsExist(ProjectInfo project);

	/**
	 * 查询方法
	 * 查询指定评审组按average递减前sum_awards条项目信息
	 * */
	@Select("SELECT DISTINCT * FROM project_info_tb,review_project_rel_tb WHERE project_info_tb.project_info_id=review_project_rel_tb.project_id AND review_project_rel_tb.review_id=#{review_group} ORDER BY project_info_tb.average DESC,project_info_id LIMIT #{sum_awards}")
	public List<ProjectInfo> getFirstProjects(@Param("review_group") int review_group,
			@Param("sum_awards") int sum_awards);

	/**
	 * 查询方法
	 * 查询指定奖项id的项目信息
	 * */
	@Select("SELECT DISTINCT * FROM project_info_tb,review_project_rel_tb,awards_tb WHERE awards_tb.review_id=review_project_rel_tb.review_id AND review_project_rel_tb.project_id=project_info_tb.project_info_id AND awards_tb.awards_id=#{awards_id}")
	public List<ProjectInfo> getProjectsByAwardId(@Param("awards_id") int awards_id);

	/**
	 * 查询方法
	 * 根据评审组id查询所有项目信息
	 * */
	@Select("SELECT * FROM review_project_rel_tb,project_info_tb WHERE review_project_rel_tb.project_id=project_info_tb.project_info_id AND review_project_rel_tb.review_id=#{review_id}")
	public List<ProjectInfo> getProjectsByReviewGroupId(@Param("review_id") int review_id);
}
